OpenEdge Development:<br />Progress 4GL Handbook Using the CAN-FIND function

The CAN-FIND function lets you determine whether a record with certain field values exists, for example as part of field validation. CAN-FIND takes a buffer name and a WHERE clause just as you would write for a FIND statement. It returns true if the record exists and false otherwise. Just as with the FIND statement, the CAN-FIND function can identify a unique record satisfying the WHERE clause, or you can include the FIRST (or even LAST) keyword before the buffer name to determine whether at least one record exists that satisfies the WHERE clause. The CAN-FIND function returns false if more than one record satisfies the selection criteria and you do not include the qualifier FIRST or LAST.

FOR EACH Order: IF NOT CAN-FIND(Customer OF Order) THEN MESSAGE "Order " Order.OrderNum " has no Customer" Order.CustNum. END. FOR EACH Customer WHERE NOT CAN-FIND (FIRST Order OF Customer): DISPLAY CustNum NAME. END.

FOR EACH Order: 
    IF NOT CAN-FIND(Customer OF Order) THEN 
        MESSAGE "Order " Order.OrderNum " has no Customer" Order.CustNum. 
END. 
FOR EACH Customer WHERE NOT CAN-FIND (FIRST Order OF Customer): 
    DISPLAY CustNum NAME. 
END.

In the first case, the code looks for Orders whose CustNum field doesn’t match any Customer record. This code would cause an error in the database’s referential integrity.

The second block looks for Customers that have no Orders. This code would probably be a valid condition, since you need to add a Customer before you start adding Orders for it.

As it turns out, the Sports2000 database doesn’t have any invalid Orders without Customers. If you add a few extra Customers to the database that aren’t in the standard database and that don’t have any Orders, the DISPLAY statement will show those, as you can see in Figure 21–3.

Why does the Progress 4GL have this function in addition to the FIND statement? CAN-FIND can return true or false simply by looking at the index entries if the selection criteria can be resolved strictly through a single index, without having to retrieve record values. After all, the statement is only asking whether a record exists (that is, if it can be found). It is not retrieving any particular field values.

Thus, CAN-FIND is beneficial and efficient only when you use it to identify records through one or more fields in a single index. If CAN-FIND has to retrieve the database records themselves, then you have lost its performance advantage over the FIND statement.